package com.sig.util;

import org.apache.commons.collections4.CollectionUtils;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * JDBC工具
 *
 * @author sig
 * @since 2019/1/26
 */
public final class JdbcUtil {
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/test_base?useUnicode=true&amp;characterEncoding=UTF-8";
    private static final String USER = "root";
    private static final String PASSWORD = "zhongcai";

    private static Connection conn;
    private static PreparedStatement ps;
    private static ResultSet rs;

    static {
        try {
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage(), e);
        }
    }

    private JdbcUtil() {
    }

    public static synchronized <T> boolean update(final String sql, final List<T> params) throws SQLException {
        ps = conn.prepareStatement(sql);
        int parameterIndex = 1;
        if (CollectionUtils.isNotEmpty(params)) {
            for (T param : params)
                ps.setObject(parameterIndex++, param);
        }
        return ps.executeUpdate() > 0;
    }

    public static <T> Map<String, Object> query(final String sql, final List<T> params) throws SQLException {
        final Map<String, Object> map = new HashMap<>();
        ps = conn.prepareStatement(sql);
        int parameterIndex = 1;
        if (CollectionUtils.isNotEmpty(params)) {
            for (Object param : params)
                ps.setObject(parameterIndex++, param);
        }
        rs = ps.executeQuery();
        final ResultSetMetaData data = rs.getMetaData();
        final int count = data.getColumnCount();
        while (rs.next()) {
            for (int index = 0; index < count; index++) {
                final String name = data.getColumnName(index + 1);
                final Object value = rs.getObject(name);
                map.put(name, value);
            }
        }
        return map;
    }

    public static <T> T query(final String sql, final List<Object> params, final Class<T> clazz) throws Exception {
        T t = null;
        ps = conn.prepareStatement(sql);
        int parameterIndex = 1;
        if (CollectionUtils.isNotEmpty(params)) {
            for (Object param : params)
                ps.setObject(parameterIndex++, param);
        }
        rs = ps.executeQuery();
        final ResultSetMetaData data = rs.getMetaData();
        final int count = data.getColumnCount();
        while (rs.next()) {
            t = clazz.getDeclaredConstructor().newInstance();
            for (int index = 0; index < count; index++) {
                final String name = data.getColumnName(index + 1);
                final Object value = rs.getObject(name);
                final Field field = clazz.getDeclaredField(name);
                field.setAccessible(true);
                field.set(t, value);
            }
        }
        return t;
    }

    public static List<Map<String, Object>> queryAll(final String sql, final List<Object> params) throws SQLException {
        final List<Map<String, Object>> list = new ArrayList<>();
        ps = conn.prepareStatement(sql);
        int parameterIndex = 1;
        if (CollectionUtils.isNotEmpty(params)) {
            for (Object param : params)
                ps.setObject(parameterIndex++, param);
        }
        rs = ps.executeQuery();
        final ResultSetMetaData data = rs.getMetaData();
        final int count = data.getColumnCount();
        while (rs.next()) {
            final Map<String, Object> map = new HashMap<>();
            for (int index = 0; index < count; index++) {
                final String name = data.getColumnName(index + 1);
                Object value = rs.getObject(name);
                if (value == null) value = "";
                map.put(name, value);
            }
            list.add(map);
        }
        return list;
    }

    public static <T> List<T> queryAll(final String sql, final List<Object> params, final Class<T> clazz) throws Exception {
        final List<T> list = new ArrayList<>();
        ps = conn.prepareStatement(sql);
        int parameterIndex = 1;
        if (CollectionUtils.isNotEmpty(params)) {
            for (Object param : params)
                ps.setObject(parameterIndex++, param);
        }
        rs = ps.executeQuery();
        final ResultSetMetaData data = rs.getMetaData();
        final int count = data.getColumnCount();
        while (rs.next()) {
            T t = clazz.getDeclaredConstructor().newInstance();
            for (int index = 0; index < count; index++) {
                final String name = data.getColumnName(index + 1);
                Object value = rs.getObject(name);
                if (value == null) value = "";
                final Field field = clazz.getDeclaredField(name);
                field.setAccessible(true);
                field.set(t, value);
            }
            list.add(t);
        }
        return list;
    }

    public static void releaseResultSet(final ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void releaseStatement(final Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void releaseConnection(final Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * @param num       数量
     * @param prefixNum 前缀个数
     * @param batch     批次
     * @param suffix    尾数
     * @param name      礼包名称
     * @return
     * @throws SQLException
     */
    public static boolean insert(final int num, final int prefixNum, final int batch, final int suffix, final String name) throws SQLException {
        final Set<Long> keyIds = CdKeyUtil.generate(num, prefixNum, batch, suffix);
        boolean flag = false;
        for (Long keyId : keyIds) {
            String sql = "INSERT INTO basecdkeyexchange(`id`,`keyId`,`name`,`suffix`,`batch`) VALUES(" + createId() + "," + keyId + ",'" + name + "'," + suffix + "," + batch + ")";
            System.out.println(sql);
            flag = update(sql, null);
        }
        return flag;
    }

    private static AtomicInteger id;

    private static int createId() throws SQLException {
        if (id == null) {
            final List<Object> params = new ArrayList<>();
            String sql = "SELECT MAX(?) FROM basecdkeyexchange";
            params.add("id");
            final Map<String, Object> map = query(sql, params);
            int maxId = (int) map.getOrDefault("id", 0);
            id = new AtomicInteger(maxId);
        }
        return id.incrementAndGet();
    }
}
